package icu.zhhll.util;


import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;

/**
 * sql构建器 用于构建条件
 * 非线程安全
 *
 * @author zh
 * @date 2023/8/15 17:48
 */
public class SqlBuilder {
    private static final String AND = " and ";
    private final StringBuilder sql = new StringBuilder();
    private final List<Object> values = new ArrayList<>();
    private final List<String> columns = new ArrayList<>();
    private final List<String> tables = new ArrayList<>();
    private String having;
    private final List<String> groupBy = new ArrayList<>();
    private final List<String> orderBy = new ArrayList<>();
    private String limit;
    private boolean distinct;

    public static SqlBuilder getInstance() {
        return new SqlBuilder();
    }

    /**
     * 得到拼好的SQL语句
     *
     * @return 拼好的SQL语句
     */
    public String getSql() {
//        String sqlString = sql.toString();
        StringBuilder where = new StringBuilder(sql);
        StringBuilder wholeSql = new StringBuilder();
        int pos = 0;
        if (values.size() > 0) {
            for (Object value : values) {
                pos = where.indexOf("?", pos);
//                pos = sqlString.indexOf('?', pos);
                if (pos == -1) {
                    break;
                }
                where.deleteCharAt(pos).insert(pos, "'" + value + "'");
//                sqlString = sqlString.substring(0, pos) + "'" + values.get(i) + "'" + sqlString.substring(pos + 1);
                pos = pos + 1;
            }
        }

        // 去除开头的and
        if (where.indexOf(AND) == 0) {
            where.delete(0, 4);
        }
//        if (StringUtils.isNotBlank(sqlString) && sqlString.startsWith(AND)) {
//            sqlString = sqlString.substring(4);
//        }

        if (columns.size() > 0) {
            wholeSql.append("SELECT ");
            if (distinct) {
                wholeSql.append(" distinct ");
            }
            wholeSql.append(StringUtils.join(columns, ","));
        }


        if (tables.size() > 0) {
            wholeSql.append(" FROM ").append(StringUtils.join(tables, ","));
        }

        if (StringUtils.isNotBlank(where)) {
            wholeSql.append(" WHERE ").append(where);
        }

        if (groupBy.size() > 0) {
            wholeSql.append(" GROUP BY ").append(StringUtils.join(groupBy, ","));
        }

        if (StringUtils.isNotBlank(having)) {
            wholeSql.append(" having ").append(having);
        }

        if (orderBy.size() > 0) {
            wholeSql.append(" ORDER BY ").append(StringUtils.join(orderBy, ","));
        }

        if (StringUtils.isNotBlank(limit)) {
            wholeSql.append(" ").append(limit);
        }


        return wholeSql.toString();
    }

    /**
     * 得到参数的值
     *
     * @return 参数的值
     */
    public Object[] getValues() {
        return values.toArray();
    }

    /**
     * 需要查询的字段
     *
     * @param fields
     * @return
     */
    public SqlBuilder select(String... fields) {
        return select(Arrays.asList(fields));
    }

    /**
     * 需要查询的字段
     *
     * @param fields
     * @return
     */
    public SqlBuilder select(Collection<String> fields) {
        columns.addAll(fields);
        return this;
    }

    /**
     * 需要查询的字段 distinct
     *
     * @param fields
     * @return
     */
    public SqlBuilder selectDistinct(String... fields) {
        return selectDistinct(Arrays.asList(fields));
    }

    /**
     * 需要查询的字段 distinct
     *
     * @param fields
     * @return
     */
    public SqlBuilder selectDistinct(Collection<String> fields) {
        distinct = true;
        columns.addAll(fields);
        return this;
    }

    /**
     * groupBy的字段
     *
     * @param groupByColumns
     * @return
     */
    public SqlBuilder groupBy(String... groupByColumns) {
        return groupBy(Arrays.asList(groupByColumns));
    }

    /**
     * groupBy的字段
     *
     * @param groupByColumns
     * @return
     */
    public SqlBuilder groupBy(Collection<String> groupByColumns) {
        groupBy.addAll(groupByColumns);
        return this;
    }

    public SqlBuilder having(String conditions) {
        having = conditions;
        return this;
    }

    /**
     * from表
     *
     * @param tableNames
     * @return
     */
    public SqlBuilder from(String... tableNames) {
        return from(Arrays.asList(tableNames));
    }

    public SqlBuilder from(Collection<String> tableNames) {
        this.tables.addAll(tableNames);
        return this;
    }

    public SqlBuilder addEqual(String name, Integer value) {
        sql.append(AND).append(name).append("=?");
        values.add(value);
        return this;
    }

    public SqlBuilder addEqual(String name, Long value) {
        sql.append(AND).append(name).append("=?");
        values.add(value);
        return this;
    }

    public SqlBuilder addEqual(String name, String value) {
        sql.append(AND).append(name).append("=?");
        values.add(value);
        return this;
    }

    public SqlBuilder addEqual(String name, Double value) {
        sql.append(AND).append(name).append("=?");
        values.add(value);
        return this;
    }

    public SqlBuilder addEqual(String name, Float value) {
        sql.append(AND).append(name).append("=?");
        values.add(value);
        return this;
    }

    public SqlBuilder addBetween(String name, String begin, String end) {
        sql.append(" and (").append(name).append(" between ? and ?)");
        values.add(begin);
        values.add(end);
        return this;
    }

    public SqlBuilder addBetween(String name, Long begin, Long end) {
        sql.append(" and (").append(name).append(" between ? and ?)");
        values.add(begin);
        values.add(end);
        return this;
    }

    public SqlBuilder addBetween(String name, Integer begin, Integer end) {
        sql.append(" and (").append(name).append(" between ? and ?)");
        values.add(begin);
        values.add(end);
        return this;
    }

    public SqlBuilder addBetween(String name, Double begin, Double end) {
        sql.append(" and (").append(name).append(" between ? and ?)");
        values.add(begin);
        values.add(end);
        return this;
    }

    public SqlBuilder addBetween(String name, Float begin, Float end) {
        sql.append(" and (").append(name).append(" between ? and ?)");
        values.add(begin);
        values.add(end);
        return this;
    }

    public SqlBuilder addGreaterEqual(String name, Integer value) {
        sql.append(AND).append(name).append(" >= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterEqual(String name, Long value) {
        sql.append(AND).append(name).append(" >= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterEqual(String name, Double value) {
        sql.append(AND).append(name).append(" >= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterEqual(String name, Float value) {
        sql.append(AND).append(name).append(" >= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterThen(String name, Integer value) {
        sql.append(AND).append(name).append(" > ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterThen(String name, Long value) {
        sql.append(AND).append(name).append(" > ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterThen(String name, Double value) {
        sql.append(AND).append(name).append(" > ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addGreaterThen(String name, Float value) {
        sql.append(AND).append(name).append(" > ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessEqual(String name, Integer value) {
        sql.append(AND).append(name).append(" <= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessEqual(String name, Long value) {
        sql.append(AND).append(name).append(" <= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessEqual(String name, Double value) {
        sql.append(AND).append(name).append(" <= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessEqual(String name, Float value) {
        sql.append(AND).append(name).append(" <= ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessThan(String name, Integer value) {
        sql.append(AND).append(name).append(" < ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessThan(String name, Long value) {
        sql.append(AND).append(name).append(" < ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessThan(String name, Double value) {
        sql.append(AND).append(name).append(" < ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addLessThan(String name, Float value) {
        sql.append(AND).append(name).append(" < ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addNotEqual(String name, String value) {
        sql.append(AND).append(name).append(" != ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addNotEqual(String name, Integer value) {
        sql.append(AND).append(name).append(" != ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addNotEqual(String name, Long value) {
        sql.append(AND).append(name).append(" != ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addNotEqual(String name, Double value) {
        sql.append(AND).append(name).append(" != ? ");
        values.add(value);
        return this;
    }

    public SqlBuilder addNotEqual(String name, Float value) {
        sql.append(AND).append(name).append(" != ? ");
        values.add(value);
        return this;
    }

    /**
     * 会将value中的*替换为%
     *
     * @param name
     * @param value
     * @return
     */
    public SqlBuilder addLike(String name, String value) {
        if (value != null) {
            value = StringUtils.replace(value, "*", "%");
        }
        sql.append(AND).append(name).append(" like ?");
        values.add(value);
        return this;
    }

    /**
     * 前缀模糊，会在value后加%
     *
     * @param name
     * @param value
     * @return
     */
    public SqlBuilder addPrefixLike(String name, String value) {
        if (value != null && !value.endsWith("%")) {
            value = value + "%";
        }
        sql.append(AND).append(name).append(" like ?");
        values.add(value);
        return this;
    }

    /**
     * 后缀模糊，会在value后加%
     *
     * @param name
     * @param value
     * @return
     */
    public SqlBuilder addSuffixLike(String name, String value) {
        if (value != null && !value.startsWith("%")) {
            value = "%" + value;
        }
        sql.append(AND).append(name).append(" like ?");
        values.add(value);
        return this;
    }

    /**
     * 全模糊，会在value前后加%
     *
     * @param name
     * @param value
     * @return
     */
    public SqlBuilder addWholeLike(String name, String value) {
        if (value != null && !value.startsWith("%")) {
            value = "%" + value;
        }
        if (value != null && !value.endsWith("%")) {
            value = value + "%";
        }
        sql.append(AND).append(name).append(" like ?");
        values.add(value);
        return this;
    }


    public SqlBuilder addIn(String name, List<String> value) {
        String[] array = {};
        return addIn(name, value.toArray(array));
    }

    public SqlBuilder addIn(String name, String[] value) {
        StringBuilder inCondition = new StringBuilder();
        for (int i = 0; i < value.length; i++) {
            if (i == value.length - 1) {
                inCondition.append('?');
            } else {
                inCondition.append("?,");
            }
        }
        sql.append(AND).append(name).append(" in (").append(inCondition).append(")");
        values.addAll(Arrays.asList(value));
        return this;
    }

    public SqlBuilder addIsNotNull(String name) {
        sql.append(AND).append(name).append(" is not null ");
        return this;
    }

    public SqlBuilder addIsNull(String name) {
        sql.append(AND).append(name).append(" is null ");
        return this;
    }

    public SqlBuilder ascOrder(String name) {
//        sql.append(" order by ").append(name);
        orderBy.add(" " + name);
        return this;
    }

    public SqlBuilder descOrder(String name) {
//        sql.append(" order by ").append(name).append(" desc");
        orderBy.add(" " + name + " desc");
        return this;
    }


    public SqlBuilder addSql(String sql) {
        this.sql.append(sql);
        return this;
    }

    public SqlBuilder addValue(Object value) {
        values.add(value);
        return this;
    }

    public SqlBuilder limit(Integer limit) {
        this.limit = " limit " + limit;
//        sql.append(" limit ").append(limit);
        return this;
    }

    public SqlBuilder limitOffset(Integer limit, Integer offset) {
        this.limit = " limit " + limit + " offset " + offset;
//        sql.append(" limit ").append(limit).append(" offset ").append(offset);
        return this;
    }
}
